/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.just.dusin.ooii.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.just.dusin.ooii.util.DateUtil;


/**
 *
 * @author Administrator
 */
public class RealDeviceDao {
    private Connection conn = null;
    public int size;

    public RealDeviceDao(Connection conn) {
        this.conn = conn;
    }
    

   public boolean doCreate(RealDeviceBean realDeviceStatus) throws Exception{
        boolean flag = false;
        PreparedStatement pstmt = null;
        
        String sql = "INSERT INTO [realdevice]([orgcode],[deviceno],[smyc],[jlyc],[wkyc],[txyc],[wlyc],[note],time,sbzt,gdyc)"
                + " VALUES (?,?,?,?,?,?,?,?,?,?,?)";
        try {
            pstmt = this.conn.prepareStatement(sql);
            pstmt.setString(1, realDeviceStatus.getOrgCode());
            pstmt.setString(2,realDeviceStatus.getDeviceNo());
            pstmt.setByte(3, realDeviceStatus.getSmyc());
            pstmt.setByte(4,realDeviceStatus.getJlyc());
            pstmt.setByte(5,realDeviceStatus.getWkyc());
            pstmt.setByte(6, realDeviceStatus.getWlyc());
            pstmt.setByte(7,realDeviceStatus.getTxyc());
            pstmt.setString(8,realDeviceStatus.getNote());
            pstmt.setString(9, realDeviceStatus.getTime());
            pstmt.setByte(10, realDeviceStatus.getSbzt());
            pstmt.setByte(11, realDeviceStatus.getGdyc());
            
            int len = pstmt.executeUpdate();
            if (len > 0) {
                flag = true;
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw e;
            }
        }
        return flag;
    }

    public boolean updateYCStatus(String status,String mineCode ,Date time) throws Exception{
        boolean flag = false;
        PreparedStatement pstmt = null;
        String sql=null;
        
        int smyc = status.charAt(0)-'0';
        int jlyc = status.charAt(1)-'0';
        int wkyc = status.charAt(2)-'0';
        int txyc = status.charAt(3)-'0';      
        int sbzt = status.charAt(4)-'0';      
        int gdyc = status.charAt(5)-'0';      
        String strtime = DateUtil.get(time,"yyyy-MM-dd HH:mm");
        sql = "UPDATE [realdevice] SET smyc=?,jlyc=?,wkyc=?,txyc=?,sbzt=?,gdyc=?,wlyc=1,time=? WHERE [orgcode]=?";           
        try {
            pstmt = this.conn.prepareStatement(sql);
            pstmt.setInt(1,smyc );
            pstmt.setInt(2,jlyc );
            pstmt.setInt(3,wkyc );
            pstmt.setInt(4,txyc );
            pstmt.setInt(5,sbzt );
            pstmt.setInt(6,gdyc );
            pstmt.setString(7,strtime);
            pstmt.setString(8, mineCode);
            
            int len = pstmt.executeUpdate();
            if (len > 0) {
                flag = true;
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw e;
            }
        }
        return flag;
    }
    //五分钟收不到数据，将设置网络通信异常
    public boolean updateWLStatus(int status,String mineCode ) throws Exception{
        boolean flag = false;
        PreparedStatement pstmt = null;
        String sql=null;
        
        sql = "UPDATE [realdevice] SET wlyc=?  WHERE [orgcode]=?";           
        try {
            pstmt = this.conn.prepareStatement(sql);
            pstmt.setInt(1,status );
            pstmt.setString(2, mineCode);
            
            int len = pstmt.executeUpdate();
            if (len > 0) {
                flag = true;
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw e;
            }
        }
        return flag;
    }
    
    
    //设置上传状态
    public boolean updateSCStatus(int status,String mineCode ) throws Exception{
        boolean flag = false;
        PreparedStatement pstmt = null;
        String sql=null;
        
        sql = "UPDATE [realdevice] SET sczt=?  WHERE [orgcode]=?";           
        try {
            pstmt = this.conn.prepareStatement(sql);
            pstmt.setInt(1,status );
            pstmt.setString(2, mineCode);
            
            int len = pstmt.executeUpdate();
            if (len > 0) {
                flag = true;
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw e;
            }
        }
        return flag;
    }
     
    public boolean doDelete(long pid) throws Exception {
        boolean flag = false;
        PreparedStatement pstmt = null;
        String sql = "DELETE FROM realdevice WHERE id=?";
        try {
            pstmt = this.conn.prepareStatement(sql);
            pstmt.setLong(1, pid);
            int len = pstmt.executeUpdate();
            if (len > 0) {
                flag = true;
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw e;
            }
        }
        return flag;
    }

    public boolean doDelete(String[] pids) throws Exception {
        for (String pid : pids) {
            long id = Long.parseLong(pid);
            doDelete(id);
        }
        return true;
    }

    public Date getCurrentTime(String orgCode) throws Exception{
        Date rtDate=null;
        PreparedStatement pstmt = null;
        String sql = "select time from  realdevice WHERE orgCode=? ";
        try {
            pstmt = this.conn.prepareStatement(sql);
            pstmt.setString (1,orgCode);
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                String time = rs.getString(1);
                if (time!=null)
                    rtDate = DateUtil.get(rs.getString(1), "yyyy-MM-dd HH:mm");
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw e;
            }
        }
        return rtDate;
    }
    
    public RealDeviceBean findByOrgCode(String orgCode) throws Exception{
        RealDeviceBean realStatus = null;
        List<RealDeviceBean> all = findAll(orgCode,0,10);
        if(all.size()>0)
            realStatus = all.get(0);        
        return realStatus;
    }
    
    public List<RealDeviceBean> findAll(String orgCode,int beginIndex, int size) throws Exception {
            List<RealDeviceBean> all = new ArrayList<RealDeviceBean>();
            PreparedStatement pstmt = null;
            PreparedStatement pstmtsize = null;
            String sqlsize = "select count(*) from  realdevice  where orgcode like '"+orgCode+"%'";

            String sql = "SELECT [id],[orgcode],smallname,[deviceno],[smyc],[jlyc],[wkyc],[txyc],[wlyc],[note],[time],sbzt,gdyc from("
                    +"SELECT realdevice.[id],realdevice.[orgcode],smallname,[deviceno],[smyc],[jlyc],[wkyc],[txyc],[wlyc],[sbzt],[gdyc],realdevice.[note],time,row_number() over(order by realdevice.id) rn  FROM [realdevice] left join org on realdevice.orgcode = org.orgcode where realdevice.orgcode like '"+orgCode+"%' ) t where t.rn>=? and t.rn<? ";

            try {
                    pstmtsize = this.conn.prepareStatement(sqlsize);
                    ResultSet rssize = pstmtsize.executeQuery(); //
                    rssize.next();
                    this.size = rssize.getInt(1);

                    pstmt = this.conn.prepareStatement(sql);			
                    pstmt.setInt(1, beginIndex+1);
                    pstmt.setInt(2, beginIndex + size+1);
                    ResultSet rs = pstmt.executeQuery(); //
                    RealDeviceBean realdevice = null;
                    while (rs.next()) { //
                            realdevice = new RealDeviceBean();
                            realdevice.setId(rs.getLong(1));
                            realdevice.setOrgCode(rs.getString(2));
                            realdevice.setSmallName(rs.getString(3));
                            realdevice.setDeviceNo(rs.getString(4));
                            realdevice.setSmyc(rs.getByte(5));
                            realdevice.setJlyc(rs.getByte(6));
                            realdevice.setWkyc(rs.getByte(7));
                            realdevice.setTxyc(rs.getByte(8));
                            realdevice.setWlyc(rs.getByte(9));
                            realdevice.setNote(rs.getString(10));
                            realdevice.setTime(rs.getString(11));
                            realdevice.setSbzt(rs.getByte(12));
                            realdevice.setGdyc(rs.getByte(13));
                            all.add(realdevice); //
                    }
            } catch (Exception e) {
                    e.printStackTrace();
                    throw e;
            } finally {
                    try {
                            pstmt.close();
                    } catch (Exception e) {
                            throw e;
                    }
            }
            return all;
    }

    public List<RealDeviceBean> findAll() throws Exception {
            List<RealDeviceBean> all = new ArrayList<RealDeviceBean>();
            PreparedStatement pstmt = null;
          

            String sql = "SELECT [id],[orgcode],[deviceno],[smyc],[jlyc],[wkyc],[txyc],[wlyc],[note] ,[time],sbzt,gdyc from realdevice";                  

            try {    
                    pstmt = this.conn.prepareStatement(sql);			                  
                    ResultSet rs = pstmt.executeQuery(); //
                    RealDeviceBean realdevice = null;
                    while (rs.next()) { //
                            realdevice = new RealDeviceBean();
                            realdevice.setId(rs.getLong(1));
                            realdevice.setOrgCode(rs.getString(2));
                            
                            realdevice.setDeviceNo(rs.getString(3));
                            realdevice.setSmyc(rs.getByte(4));
                            realdevice.setJlyc(rs.getByte(5));
                            realdevice.setWkyc(rs.getByte(6));
                            realdevice.setTxyc(rs.getByte(7));
                            realdevice.setWlyc(rs.getByte(8));
                            realdevice.setNote(rs.getString(9));
                            realdevice.setTime(rs.getString(10));
                            realdevice.setSbzt(rs.getByte(11));
                            realdevice.setGdyc(rs.getByte(12));
                            all.add(realdevice); //
                    }
            } catch (Exception e) {
                    e.printStackTrace();
                    throw e;
            } finally {
                    try {
                            pstmt.close();
                    } catch (Exception e) {
                            throw e;
                    }
            }
            return all;
    }
    
    public int getTotalSize() {
        return size;
    }
}
